import random,time,requests,json

# from db import db
from model.models import db
from flask import Blueprint, request, jsonify, send_file, redirect
from flask_restful import Api, Resource,reqparse
from captcha.image import ImageCaptcha
from utils.myredis import r
from utils.sms import send_message
from utils.myjwt import myjwt
from model.models import *

course_dp = Blueprint('course_dp',__name__)
api = Api(course_dp)

#类别
@course_dp.route("/ctype")
def ctype():
    ctypelist = []
    # 获取缓存中数据
    r.del_key("ctype")
    ctype = r.get_str("ctype")
    # 判断是否存在
    if ctype:
        ctypelist = json.loads(ctype)
    else:
        sql = Ctype.query.all()
        for i in sql:
            d = {
                "id": i.id,
                "name":i.name
            }
            ctypelist.append(d)
        # 存入缓存
        r.set_str("ctype", json.dumps(ctypelist))
    return ctypelist


# 标签
def getTaglist():
    taglist = []
    r.del_key("tage")
    tag = r.get_str("tage")
    if tag:
        taglist = json.loads(tag)
    else:
        sql = Tags.query.all()
        for i in sql:
            d = {
                "id":i.id,
                "name":i.name
            }
            taglist.append(d)
        r.set_str("tage", json.dumps(taglist))
    return taglist

#分页
@course_dp.route("/getHomeGourse")
def getHomeGourse1():
    # 获取类型
    ctypelist = ctype()
    # 获取标签
    taglist = getTaglist()
    # 获取当前页面
    page = request.args.get("page")
    # 获取查询条件
    typeid = request.args.get("type")
    tagid = request.args.get("tagid")
    online = request.args.get("online")
    # 排序条件
    orderbyid = request.args.get("orderbyid")

    where = ""
    if int(typeid) > 0:
        where = where + " and c.ctype=" + typeid

    if int(online) > 0:
        where = where + " and c.views=" + online

    # res = db.session.execute("select * from user where id=1")
    # print(res.fetchall())
    # print(res.fetchone())

    if int(tagid) > 0:
        tsql = "select count(c.id) as tcount from course as c inner join course_tag as cl on cl.courseid=tcount where cl.tagid=" + tagid
        sql = "select * from course as c inner join course_tag as cl on cl.courseid=c.id  where cl.tagid=" + tagid
    else:
        sql = "select * from course as c where 1=1"
        tsql = "select count(c.id) as tcount from course as c  where 1=1"

    if where:
        sql = sql + " " + where
        tsql = tsql + " " + where

    if int(orderbyid) > 0:
        if int(orderbyid) == 1:
            sql = sql + " order by c.id desc"
        else:
            sql = sql + " order by c.is_publish desc"

    # 查询总共有多少条
    tsql = db.session.execute(tsql)
    total = 0
    for i in tsql:
        total = i[0]

    page_size = 2
    start = (int(page) - 1) * page_size
    clist = sql + " limit %d,%d" % (start, page_size)
    clist = db.session.execute(clist)
    courselist = []
    for i in clist:
        d = {
            "id":i[0],
            "create_time":i[1],
            "update_time":i[2],
            "name":i[3],
            "url":i[4],
            "descrip":i[5],
            "tcount":i[6],
            "views":i[7],
            "is_publish":i[8],
            "ctype":i[9],
            "buy":i[10]
        }
        courselist.append(d)

    return jsonify({"code": 200, "tlist": ctypelist, 'taglist': taglist, 'clist': courselist, "total": total,
                    "page_size": page_size})


# 课程详情页面
@course_dp.route("/detail")
def detail():
    # 获取课程id
    id = request.args.get('id')
    # 从缓存中查询课程及章节信息
    key = "course"+str(id)
    skey = "jie"+str(id)
    r.del_key(key)
    r.del_key(skey)
    course = r.get_str(key)
    if course:
        print("缓存")
        jie = r.get_str(skey)
        return jsonify({"code": 200, 'clist': json.loads(course), 'slist': json.loads(jie)})
    # 如果存在直接返回，如果不存在查询数据库
    else:
        print("查询")
        sql = "select c.id,j.id as jid,c.name as cname,c.descrip,c.tcount,j.name,j.url,j.type,c.ctype from course as c inner join jie as j on c.id=j.courseid where c.id=%d order by j.tcount asc" % (
            int(id))
        res = db.session.execute(sql)
        jie = []
        course = []
        for i in res:
            # 获取课程下所有的章节
            d = {
                "id": i['jid'], "name": i['name'], "url": i['url'], "courseid": i['id'], "type": i['type']
            }
            # 获取课程信息
            c = {
                "id": i['id'], "type": i["ctype"], "name": i['cname'],
                'descrip': i['descrip'], 'tcount': i['tcount']
            }
            jie.append(d)
            course.append(c)
        # 数据解析存入redis
        r.set_str(key, json.dumps(course))
        r.set_str(skey, json.dumps(jie))
        return jsonify({"code": 200, 'clist': course, 'slist': jie})


@course_dp.route("/getVideo")
def getVideo():
    # 获取用户id和章节id
    courseid = request.args.get("courseid")
    userid = request.args.get("userid")
    id = request.args.get("id")
    # 通过用户表查询用户级别
    sql = "select level from user where id=%d"%(int(userid))
    user = db.session.execute(sql)
    # 通过课程id查询课程的类型
    key = "jie"+str(courseid)
    course = r.get_str(key)
    if course:
        course=json.loads(course)
        for i in course:
            ctype=i["type"]
            if int(ctype) == 2:
                # 会员可看
                for j in user:
                    user = j["level"]
                    if int(user) != 2:
                        return jsonify({"code": 10011, "msg":"此用户没有权限观看"})
                    # 课程type=3  需要购买课程
            if int(ctype) == 3:
                bsql = "select * from vipuser_course where userid=%d and courseid=%d"%(int(userid),int(courseid))
                bres = db.session.execute(bsql)
                if not bres:
                    return jsonify({"code": 10012, "msg":"此课程需要单独购买"})
            # 获取此课程下的所有章节
            skey = "jie"+str(courseid)
            jie = json.loads(r.get_str(skey))
            for i in jie:
                if int(i["id"]) == int(id):
                    url = i["url"]
            # 返回此章节播放的url
            return jsonify({"code": 200, "msg":"获取成功", "url":url})
    else:
        return jsonify({"code":10010})


# 获取训练营里面得所有数据
@course_dp.route('/getxly')
def getxly():
    sql = 'select * from tcourse'
    res = db.session.execute(sql)
    print(res)
    return jsonify({'code':200,'msg':'ok','data':res})


#添加购物车
@course_dp.route("/addcart",methods=['POST'])
def addcart():
    data = reqparse.RequestParser()
    data.add_argument("userid")
    data.add_argument("goodsid")
    args = data.parse_args()
    userid = int(args["userid"])
    id = int(args["goodsid"])
    # pkey = "mycart"+str(userid)
    # r.hash_add(pkey,goodsid,userid)
    if not all([userid,id]):
        return jsonify({'code':400,'msg':'数据不存在'})

    cart = Cart.query.filter(Cart.id == id).first()
    if cart:
        sql = "update cart set count=count+1 where cart.id=%d"%(id)
        db.session.add(sql)
        db.session.commit()
        return jsonify({"code": 200, "msg": "加入购物车成功"})
    else:
        count = 1
        sql = "insert into cart(userid,cid,count) values('%d','%d','%d')" % (userid,id,int(count))
        db.session.add(sql)
        db.session.commit()
        return jsonify({"code":200,"msg":"加入购物车成功"})


# 我的购物车
@course_dp.route("/gettcourse")
def getcart():
    userid = request.args.get("userid")
    cartlist = []
    sql = "select * from cart where cart.userid=%d"% int(userid)
    res = db.session.execute(sql)
    for i in res:
        sql = "select * from tcourse where tcourse.id=%d"%int(i['cid'])
        res = db.session.execute(sql)
        for j in res:
            d ={
                "id":j["id"],
                "title":j["title"],
                "price":j["price"],
                "url":j["url"],
                "count":i["count"],
                "amount":(j["price"] * i["count"])
            }
            cartlist.append(d)
    return jsonify({"code": 200, "msg":"ok","cartlist": cartlist})

# 购物车数量加
@course_dp.route('/addcount',methods=["POST"])
def Add_count():
    req = reqparse.RequestParser()
    req.add_argument('userid')
    req.add_argument('id')
    args = req.parse_args()
    userid = int(args['userid'])
    id = int(args['id'])

    if not all({args['id'], args['userid']}):
        return jsonify({'code': 400, 'msg': '数据不存在'})

    # sql = "select * from cart where userid = %d and cid = %d" % (userid, id)
    # res = db.session.execute(sql)
    res = Cart.query.filter(Cart.userid==userid,Cart.cid==id).first()
    if res:
        # sql1 = "update cart set count=count+1 where userid=%d and cid=%d" % (res.userid, int(args['id']))
        # db.session.add(sql1)
        counts = res.count+1
        res.count = counts
        db.session.commit()
    return jsonify({'code':200,'msg':'ok'})


# 购物车数量减
@course_dp.route('/mincount',methods=["POST"])
def Min_count():
    req = reqparse.RequestParser()
    req.add_argument('userid')
    req.add_argument('id')
    args = req.parse_args()
    userid = int(args['userid'])
    id = int(args['id'])

    if not all({args['id'], args['userid']}):
        return jsonify({'code': 400, 'msg': '数据不存在'})

    # sql = "select * from cart where userid = %d and cid = %d" % (userid, id)
    # res = db.session.execute(sql)
    res = Cart.query.filter(Cart.userid==userid,Cart.cid==id).first()
    if res:
        # sql1 = "update cart set count=count+1 where userid=%d and cid=%d" % (res.userid, int(args['id']))
        # db.session.add(sql1)
        counts = res.count-1
        res.count = counts
        db.session.commit()
    return jsonify({'code':200,'msg':'ok'})


# 提交订单
from utils.sms import get_ali_object
@course_dp.route("/pay",methods=["POST"])
def pay():
    data=json.loads(request.data)
    userid=int(data["userid"])
    courseid=int(data["courseid"])
    sum=0
    sql = "select * from course where id=%d" % courseid
    res=db.session.execute(sql)
    for i in res:
        cartlist = {"id": i['id'],
        "create_time": i['create_time'],
        "update_time": i['update_time'],
        "name": i['name'],
        "url": i['url'],
        "descrip": i['descrip'],
        "tcount": i['tcount'],
        "views": i['views'],
        "is_publish": i['is_publish'],
        "ctype": i['ctype'],
        "buy": i['buy'],
        "price":i["price"]}
    sum+=float(cartlist["price"])
    # 订单号
    number = int(str(userid) + str(random.randint(1000, 9999)))
    create_time = datetime.datetime.strftime(datetime.datetime.now(), "%Y-%m-%d %H:%M:%S")
    update_time = datetime.datetime.strftime(datetime.datetime.now(), "%Y-%m-%d %H:%M:%S")
    status = 2
    try:
        usql = Vipuser_course(courseid=courseid,userid=userid)
        db.session.add(usql)
        isql = Orders(create_time=create_time,update_time=update_time,userid=userid,courseid=courseid,number=number,tmoney=sum,status=status,price=sum)
        db.session.add(isql)
        print(">><<")
        db.session.commit()
    except:
        db.session.rollback()
    # 支付
    alipay = get_ali_object()
    query_params = alipay.direct_pay(
        subject=str(userid) + "购买课程",  # 商品简单描述
        out_trade_no=number,  # 用户购买的商品订单号（每次不一样） 20180301073422891
        total_amount=sum,  # 交易金额(单位: 元 保留俩位小数)
    )
    pay_url = "https://openapi.alipaydev.com/gateway.do?{0}".format(query_params)  # 支付宝网关地址（沙箱应用）
    return jsonify({
        'code': 200,
        'url': pay_url
    })
@course_dp.route("/callback")
def callback():
    try:
        sql = "update orders set status=2"
        db.session.add(sql)
        db.session.commit()
    except:
        db.session.rollback()
    # 跳转到个人中心
    return redirect('http://localhost:8080/#/')